﻿Imports Microsoft.VisualBasic
Imports System.Data



Public Class DatabaseCommon

    ' DatabaseCommon is meant ot be an abstract class, instantiated objects
    ' representing tables or views in the database should inherit from this class

    Dim myConnectionString As String
    Dim ApplicationSettings As New AppSettingsReader
    Dim myValidationMessages As Collection

    Public Property validationMessages() As Collection

        ' This class tracks a collection of messages that
        ' are set during validation of the datbase object

        Get
            Return myValidationMessages
        End Get

        Set(ByVal value As Collection)
            myValidationMessages = value
        End Set
    End Property

    Public Property ConnectionString() As String
        Get
            Return myConnectionString
        End Get

        Set(ByVal value As String)
            myConnectionString = value
        End Set
    End Property

    Public Function isOLEDBConnection() As Boolean

        ' Helps to handle both SQL and OleDB connections
        If ConnectionString.ToUpper.Contains("OLEDB") Then Return True
        Return False

    End Function

    Private Function Connection() As Object

        If isOLEDBConnection() Then
            Dim conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Return conn
        Else
            Dim conn As New System.Data.SqlClient.SqlConnection(ConnectionString)
            Return conn
        End If
    End Function

    Private Function Command() As Object

        If isOLEDBConnection() Then
            Dim cmd As New System.Data.OleDb.OleDbCommand
            Return cmd
        Else
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Return cmd
        End If

    End Function

    Private Function Adapter(ByVal cmd As Object) As Object

        If isOLEDBConnection() Then
            Dim a As New System.Data.OleDb.OleDbDataAdapter(cmd)
            Return a
        Else
            Dim a As New SqlClient.SqlDataAdapter(cmd)
            Return a
        End If

    End Function

    Public Sub New()

        ' Gets Connection from Application Settings
        ' This code may be be modified if the ConnectionString is not obtained
        ' From the Applications Settings or uses a different key.

        Try
            ConnectionString = ApplicationSettings.GetValue("Connectionstring", GetType(String))
        Catch
            ' This is the case that the ConnectionString would be entered by a user
            ' No action needs to be performed here
        End Try
    End Sub

    Public Sub New(ByVal aConnectionString As String)

        ' Gets Connection as passed by user
        ConnectionString = aConnectionString

    End Sub

    Public Sub addValidationMessage(ByVal vm As String)

        validationMessages.Add(vm)

    End Sub

    Public Sub clearValidationMessages()

        validationMessages.Clear()

    End Sub

    Public Function ExecuteProcedure(ByVal spName As String, ByVal spParams As Hashtable, Optional ByVal returns As Boolean = True) As DataSet

        ' Create the connection

        Dim conn = Connection()

        ' Create the  Command Object (this can use OLEDB or SQL COmmand Objects)

        Dim sqlCommand1 = Command()
        sqlCommand1.connection = conn
        sqlCommand1.CommandText = spName
        sqlCommand1.CommandType = CommandType.StoredProcedure

        ' Create a Data Adapter to work with the dataset

        Dim sqlAdapter1 = Adapter(sqlCommand1)

        ' Fill the SQL command object with the parameters

        Dim item As IDictionaryEnumerator = spParams.GetEnumerator

        While item.MoveNext
            sqlCommand1.Parameters.Add(item.Key, item.Value)
        End While

        conn.Open()

        If returns Then
            Dim ds As New DataSet
            sqlAdapter1.Fill(ds)
            sqlCommand1.Dispose()
            conn.Close()

            Return ds
        Else

            sqlCommand1.ExecuteNonQuery()
            sqlCommand1.Dispose()
            conn.Close()

            Return Nothing
        End If

    End Function

    Public Function ExecuteProcedure(ByVal spName As String, Optional ByVal returns As Boolean = True) As DataSet

        ' Create the connection

        Dim conn = Connection()

        ' Create the  Command Object

        Dim sqlCommand1 = Command()

        sqlCommand1.connection = conn
        sqlCommand1.CommandText = spName
        sqlCommand1.CommandType = CommandType.StoredProcedure

        ' Create an adapter to work with the dataset

        Dim sqlAdapter1 = Adapter(sqlCommand1)
        conn.Open()

        If returns Then
            Dim ds As New DataSet

            sqlAdapter1.Fill(ds)
            sqlCommand1.Dispose()
            conn.Close()

            Return ds
        Else

            sqlCommand1.ExecuteNonQuery()
            sqlCommand1.Dispose()
            conn.Close()

            Return Nothing
        End If

    End Function

    Public Function ExecuteSQL(ByVal sql As String) As DataSet

        ' Create the connection

        Dim conn = Connection()

        ' Create the  Command Object

        Dim sqlCommand1 = Command()
        sqlCommand1.connection = conn
        sqlCommand1.CommandText = sql
        sqlCommand1.CommandType = CommandType.Text

        ' Create an adapter to work with the dataset

        Dim sqlAdapter1 = Adapter(sqlCommand1)
        conn.Open()

        Dim ds As New DataSet

        sqlAdapter1.Fill(ds)
        sqlCommand1.Dispose()
        conn.Close()

        Return ds

    End Function

End Class
